# This is a sample Python script.

# Press ⌃R to execute it or replace it with your code.
# Press Double ⇧ to search everywhere for classes, files, tool windows, actions, and settings.
# !/usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql
import ast

# Press the green button in the gutter to run the script.
if __name__ == '__main__':

    my_dict = {
        "/phone/customer": "phone:customer",
        "/phone/product": "phone:product",
        "/phone/product/customer": "phone:product-customer",
        "/phone/supplier": "phone:supplier",
        "/phone/price": "phone:price",
        "/phone/price/supplier": "phone:product-customer",
        "/phone/qrcode": "phone:qrcode",
        "/phone/order": "phone:order",
        "/phone/import": "phone:import",
        "/doudian/order": "doudian:order",
        "/phone/tsc": "phone:tsc",

    }

    conn = pymysql.connect(
        host='192.168.91.137',
        user='root',
        password='123456',
        port=3306,
        database='ry-yin-tel'
    )
    # 创建游标，查询数据以元组形式返回
    # cursor = conn.cursor()

    # 创建游标，查询数据以字典形式返回
    cursor = conn.cursor(pymysql.cursors.DictCursor)

    sql = "select * from sys_menu t  where t.menu_type='C' and t.menu_id > 1000 and t.menu_id not in(select distinct  a.parent_id from sys_menu a where a.menu_type='F')"
    try:
        cursor.execute(sql)
        result = cursor.fetchall()  # 返回所有数据
        # result = cursor.fetchone()  # 返回一行数据
        # result = cursor.fetchmany(2)  # fetchmany(size) 获取查询结果集中指定数量的记录，size默认为1
        # print(result)

        for r in result:
            # print( type(r))
            # print(r)
            # print(r['url'])

            if (r['url'] in my_dict.keys()):
                # print(r)
                # print(my_dict.get(r['url']))

                s1 = "update sys_menu  set perms="
                sql_body1 = "'%s:view'" % (my_dict.get(r['url']))
                ed = "  where menu_id=%s ;" % (r['menu_id'])
                s = s1 + sql_body1 + ed
                print(s)

                s1 = 'insert into sys_menu (menu_name, parent_id, perms, order_num, url, menu_type, visible,  icon, create_by, create_time, update_by, update_time, remark)  values  ('
                sql_body1 = "'%s',%s,'%s:list' " % (r['menu_name'] + '查询', r['menu_id'], my_dict.get(r['url']))
                ed = ",'1',  '#',  'F', '0',      '#', 'admin', sysdate(), '', null, '');"
                s = s1 + sql_body1 + ed
                print(s)

                s1 = 'insert into sys_menu (menu_name, parent_id, perms, order_num, url, menu_type, visible,  icon, create_by, create_time, update_by, update_time, remark)  values  ('
                sql_body1 = "'%s',%s,'%s:add' " % (r['menu_name'] + '新增', r['menu_id'], my_dict.get(r['url']))
                ed = ",'2',  '#',  'F', '0',      '#', 'admin', sysdate(), '', null, '');"
                s = s1 + sql_body1 + ed
                print(s)

                s1 = 'insert into sys_menu (menu_name, parent_id, perms, order_num, url, menu_type, visible,  icon, create_by, create_time, update_by, update_time, remark)  values  ('
                sql_body1 = "'%s',%s,'%s:edit' " % (r['menu_name'] + '修改', r['menu_id'], my_dict.get(r['url']))
                ed = ",'3',  '#',  'F', '0',      '#', 'admin', sysdate(), '', null, '');"
                s = s1 + sql_body1 + ed
                print(s)

                s1 = 'insert into sys_menu (menu_name, parent_id, perms, order_num, url, menu_type, visible,  icon, create_by, create_time, update_by, update_time, remark)  values  ('
                sql_body1 = "'%s',%s,'%s:remove' " % (r['menu_name'] + '删除', r['menu_id'], my_dict.get(r['url']))
                ed = ",'4',  '#',  'F', '0',      '#', 'admin', sysdate(), '', null, '');"
                s = s1 + sql_body1 + ed
                print(s)

                s1 = 'insert into sys_menu (menu_name, parent_id, perms, order_num, url, menu_type, visible,  icon, create_by, create_time, update_by, update_time, remark)  values  ('
                sql_body1 = "'%s',%s,'%s:export' " % (r['menu_name'] + '导出', r['menu_id'], my_dict.get(r['url']))
                ed = ",'5',  '#',  'F', '0',      '#', 'admin', sysdate(), '', null, '');"
                s = s1 + sql_body1 + ed
                print(s)


    except Exception as e:
        conn.rollback()
        print(e)



    finally:
        cursor.close()
        conn.close()
